LEFT and RIGHT JOIN Solutions

This section is a walkthrough of those final two problems in the previous concept. First, another look at the two tables we are working with:

INNER JOIN Question

The questions are aimed to assure you have a conceptual idea of what is happening with LEFT and INNER JOINs before you need to use them for more difficult problems.

For an INNER JOIN like the one here:

SELECT c.countryid, c.countryName, s.stateName
FROM Country c
JOIN State s
ON c.countryid = s.countryid;

We are essentially JOINing the matching PK-FK links from the two tables, as shown in the below image.

The resulting table will look like:

LEFT JOIN Question

The questions are aimed to assure you have a conceptual idea of what is happening with LEFT and INNER JOINs before you need to use them for more difficult problems.

For a LEFT JOIN like the one here:

SELECT c.countryid, c.countryName, s.stateName
FROM Country c
LEFT JOIN State s
ON c.countryid = s.countryid;

We are essentially JOINing the matching PK-FK links from the two tables, as we did before, but we are also pulling all the additional rows from the Country table even if they don't have a match in the State table. Therefore, we obtain all the rows of the INNER JOIN, but we also get additional rows from the table in the FROM.

The resulting table will look like:

FINAL LEFT JOIN Note

If we were to flip the tables, we would actually obtain the same exact result as the JOIN statement:

SELECT c.countryid, c.countryName, s.stateName
FROM State s
LEFT JOIN Country c
ON c.countryid = s.countryid;

This is because if State is on the LEFT table, all of the rows exist in the RIGHT table again.

The resulting table will look like: